<?php
/**
 * Ip to country database from: http://ip-to-country.webhosting.info/node/view/5
 * @package DomBase
 * @author Oliver Leisalu
 */
class DomIpToCountry {
	
	const TABLE_NAME = 'ip_to_country';

	/**
	 * Returns the last date(Y-m-d H:i:s) when table was updated.
	 * @return String
	 */
	public static function getLastUpdated() {
		$comment = DomSql::selectOneQuery('SHOW CREATE TABLE '.self::TABLE_NAME);
		$comment = $comment['Create Table'];
		$p = strpos($comment, 'COMMENT=\'');
		if( !$p ) {
			return '';
		}
		$comment = substr($comment, $p + 9,-1);
		return substr($comment, strlen($comment)-19);
	}
	/**
	 * Returns two letter country code
	 * @param String $ip
	 * @return String
	 */
	public static function getCountryCode2( $ip = NULL  ) {
		$data = self::getCountryData( $ip );
		return $data['countryCode2'];
	}
	/**
	 * Returns three letter country code
	 * @param String $ip
	 * @return String
	 */
	public static function getCountryCode3( $ip = NULL  ) {
		$data = self::getCountryData( $ip );
		return $data['countryCode3'];
	}
	/**
	 * Returns country name
	 * @param String $ip
	 * @return String
	 */
	public static function getCountryName( $ip = NULL  ) {
		$data = self::getCountryData( $ip );
		return $data['countryName'];
	}
	/**
	 * Returns array with: countryCode2, countryCode3, countryName
	 * @param String $ip
	 * @return Array
	 */
	public static function getCountryData( $ip = NULL ) {
		if( $ip === NULL ) {
			$ip = $_SERVER['REMOTE_ADDR'];
		}
		$ipl = ip2long( $ip );
		if( $ipl == -1 ) {
			return null;
		}
		$data = DomSql::selectOneQuery('
			SELECT countryCode2,countryCode3,countryName
			FROM '.self::TABLE_NAME.' 
			WHERE ipFrom <= '.$ipl.' and ipTo >= '.$ipl.'
			LIMIT 1');
		
		if( DOM_IP_TO_COUNTRY_LOG === true ) {
			$log = new DomDBLogger('ip_to_country');
			$log->log( $ip, $data ? $data['countryName'].';'.$data['countryCode2'].';'.$data['countryCode3'] : NULL, $data ? 0 : 1 );
		}
		return $data;
	}
	
	private static function insertBulk( $bulk ) {
		DomSql::query('INSERT INTO '.self::TABLE_NAME.'  (ipFrom,ipTo,countryCode2,countryCode3,countryName) VALUES '.substr($bulk,0,-1));
	}
	
	public static function updateDatabase( $csvFile = '' ) {
		
		DomSql::query('CREATE TABLE IF NOT EXISTS '.self::TABLE_NAME.'  (
			ipFrom int unsigned,
			ipTo int unsigned,
			countryCode2 char(2),
			countryCode3 char(3),
			countryName varchar(50)
		)');
		
		if( $csvFile == '' ) {
			$csvFile = dirname(__FILE__).'/ip-to-country.csv';
		}
		
		$handle = fopen($csvFile, "r");
		if( !$handle ) {
			return false;
		}
		
		try {
			DomSql::query('TRUNCATE '.self::TABLE_NAME.' ');
			DomSql::query('ALTER TABLE '.self::TABLE_NAME.' DROP INDEX search');
		} catch( Exception $e ) {}
		
		$i = 0;
		$bulk = '';
		while( ($data = fgetcsv($handle, 1000, ",")) !== false) {
			
			$bulk .= '(';
			$bulk .= (double)$data[0].',';
			$bulk .= (double)$data[1].',';
			$bulk .= '"'.addslashes($data[2]).'",';
			$bulk .= '"'.addslashes($data[3]).'",';
			$bulk .= '"'.addslashes($data[4]).'"';
			$bulk .= '),';
			
			$i++;
			if( $i%2000 == 0 ) {
				self::insertBulk( $bulk );
				$bulk = '';
			}
	    }
	  	self::insertBulk( $bulk );
		fclose($handle);
		DomSql::query('ALTER TABLE '.self::TABLE_NAME.' COMMENT = \'Database last updated on '.date('Y-m-d H:i:s').'\';');
		DomSql::query('ALTER TABLE '.self::TABLE_NAME.'  ADD INDEX search (ipFrom,ipTo)');
	}
	
}